﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using MockPrjTeam2.Common;
using MockPrjTeam2.Entity;
using System.Data;
namespace MockPrjTeam2.DAO
{
    public class SupportingMeterialDao
    {
        private static SupportingMeterialDao supportingMeterialDao;
        public static SupportingMeterialDao Instances
        {
            get
            {
                if (supportingMeterialDao == null)
                    supportingMeterialDao = new SupportingMeterialDao();

                return supportingMeterialDao;
            }
        }
        //method GetSupportingMeterialByOrgId()
        public DataTable GetSupportingMeterialByOrgId(int orgId)
        {
            const string query = "select [User].Account,Url,SuportingMeterial.Description,Type,AddDate,SuportingMeterialID,SuportingMeterial.IsActive,SuportingMeterial.UserID from SuportingMeterial inner join [User] on SuportingMeterial.UserID=[User].UserID where OrgID=@OrgID and SuportingMeterial.IsActive=1 order by SuportingMeterial.Url asc";
          //const  string query = "select [User].Account,Url,SuportingMeterial.Description,Type,AddDate from SuportingMeterial inner join [User] on SuportingMeterial.UserID=[User].UserID where OrgID=@OrgId  ";
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@OrgID", SqlDbType.Int);
            sqlParameters[0].Value = orgId;
            using (var helper = new SqlHelper())
            {
                return helper.ExecuteSelectQuery(query,sqlParameters);
            }
        }
  
        //method GetSupportingMaterialInActive()
        public DataTable GetSupportingMaterialInActive(int orgId)
        {

            string query = "select [User].Account,Url,SuportingMeterial.Description,Type,AddDate,SuportingMeterialID,SuportingMeterial.IsActive from SuportingMeterial inner join [User] on SuportingMeterial.UserID=[User].UserID where OrgID=@OrgID order by SuportingMeterial.Url asc";
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@OrgID", SqlDbType.Int);
            sqlParameters[0].Value = orgId;
            using (var helper = new SqlHelper())
            {
                return helper.ExecuteSelectQuery(query, sqlParameters);
            }
        }
       //method SetActiveSupportingMaterial
        public bool SetActiveSupportingMaterial(int supportingMaterialId, bool status)
        {
            bool reply;
            string query = @"update SuportingMeterial set IsActive=@IsActive where SuportingMeterialID=@SpId ";
            using (var helper = new SqlHelper())
            {
                SqlParameter[] sqlParameters = new SqlParameter[2];
                sqlParameters[0] = new SqlParameter("@SpId", SqlDbType.Int);
                sqlParameters[0].Value = supportingMaterialId;
                sqlParameters[1] = new SqlParameter("@IsActive", SqlDbType.Bit);
                sqlParameters[1].Value = status;
                reply = helper.ExecuteUpdateQuery(query, sqlParameters);
            }
            return reply;
        }
        //method GetSupportingById()
        public DataTable GetSupportingById(int idSupporting)
        {
            string query =
                "select [User].Account,Url,SuportingMeterial.Description,Type,AddDate,SuportingMeterialID,SuportingMeterial.IsActive,[User].UserID,SuportingMeterial.OrgID from SuportingMeterial inner join [User] on SuportingMeterial.UserID=[User].UserID where SuportingMeterialID=@SuportingMeterialID";

            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@SuportingMeterialID", SqlDbType.Int);
            sqlParameters[0].Value = idSupporting;
            using (var helper = new SqlHelper())
            {
                return helper.ExecuteSelectQuery(query, sqlParameters);
            }
           
        }
        //method AddSupportingMaterial()
        public bool AddSupportingMaterial(SupportingMeterial supportingMeterial)
        {
            bool retVal = false;
            try
            {
                
                string query =
                    @"insert into SuportingMeterial(UserID,OrgID,Url,Description,Type,AddDate) values(@UserID,@OrgID,@Url,@Description,@Type,@AddDate)";
                using (var helper = new SqlHelper())
                {
                    SqlParameter[] sqlParameters = new SqlParameter[6];
                    sqlParameters[0] = new SqlParameter("@UserID", SqlDbType.Int);
                    sqlParameters[0].Value = supportingMeterial.UserID;
                    sqlParameters[1] = new SqlParameter("@OrgID", SqlDbType.Int);
                    sqlParameters[1].Value = supportingMeterial.OrgId;
                    sqlParameters[2] = new SqlParameter("@Url", SqlDbType.NVarChar);
                    sqlParameters[2].Value = supportingMeterial.Url;
                    sqlParameters[3] = new SqlParameter("@Description", SqlDbType.NVarChar);
                    sqlParameters[3].Value = supportingMeterial.Description;
                    sqlParameters[4] = new SqlParameter("@Type", SqlDbType.NVarChar);
                    sqlParameters[4].Value = supportingMeterial.Type;
                    sqlParameters[5] = new SqlParameter("@AddDate", SqlDbType.DateTime);
                    sqlParameters[5].Value = supportingMeterial.AddDate;
                   helper.ExecuteInsertQuery(query, sqlParameters);
                }
                return true;
            }
            catch (Exception ex)
            {
                ////Log here
            }
            return retVal;
           
        }
        //method UpdateSupportingMaterial()
        public bool UpdateSupportingMaterial(SupportingMeterial supportingMeterial)
        {
            bool retVal1 = false;
            try
            {
                string query =
                    @"update SuportingMeterial set Url=@Url,[Description]=@Description,Type=@Type where SuportingMeterialID=@SuportingMeterialID ";
                using (var helper = new SqlHelper())
                {
                    SqlParameter[] sqlParameters = new SqlParameter[4];
                    sqlParameters[0] = new SqlParameter("@Url", SqlDbType.NVarChar);
                    sqlParameters[0].Value = supportingMeterial.Url;
                    sqlParameters[1] = new SqlParameter("@Description", SqlDbType.NVarChar);
                    sqlParameters[1].Value = supportingMeterial.Description;
                    sqlParameters[2] = new SqlParameter("@Type", SqlDbType.NVarChar);
                    sqlParameters[2].Value = supportingMeterial.Type;
                    sqlParameters[3] = new SqlParameter("@SuportingMeterialID", SqlDbType.Int);
                    sqlParameters[3].Value = supportingMeterial.SuportingMeterialId;
                    helper.ExecuteUpdateQuery(query, sqlParameters);
                }
                return true;
            }
            catch(Exception ex)
            {
                
            }
            return retVal1;
        }
        //method SetActive SupportingMateria()
        public bool SetActiveSupportingMateria(int SupportingMaterialId, bool status)
        {
            bool reply;
            string query = @"update SuportingMeterial set IsActive=@IsActive where SuportingMeterialID=@SuportingMeterialID ";
            using (var helper = new SqlHelper())
            {
                SqlParameter[] sqlParameters = new SqlParameter[2];
                sqlParameters[0] = new SqlParameter("@SuportingMeterialID", SqlDbType.Int);
                sqlParameters[0].Value = SupportingMaterialId;
                sqlParameters[1] = new SqlParameter("@IsActive", SqlDbType.Bit);
                sqlParameters[1].Value = status;
                reply = helper.ExecuteUpdateQuery(query, sqlParameters);
            }
            return reply;
        }
        //method SetSupportingMateriaInActive()
        public bool SetSupportingMateriaInActive(int SupportingMaterialId, bool status)
        {
            bool reply;
            string query = @"update SuportingMeterial set IsActive=@IsActive where SuportingMeterialID=@SuportingMeterialID ";
            using (var helper = new SqlHelper())
            {
                SqlParameter[] sqlParameters = new SqlParameter[2];
                sqlParameters[0] = new SqlParameter("@SuportingMeterialID", SqlDbType.Int);
                sqlParameters[0].Value = SupportingMaterialId;
                sqlParameters[1] = new SqlParameter("@IsActive", SqlDbType.Bit);
                sqlParameters[1].Value = status;
                reply = helper.ExecuteUpdateQuery(query, sqlParameters);
            }
            return reply;
        }
    }
}